《廖雪峰：SQL教程》笔记

发表于 | | 热度: ℃
字数统计: 2.5k 字 | 阅读时长 ≈ 9 分钟

什么是SQL

简单地说，SQL就是访问和处理关系数据库的计算机标准语言
具体来说，SQL是结构化查询语言Structured Query Language的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。

数据库有哪三种模型

层次模型：就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树
网状模型：把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网
关系模型：把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表

主流的关系数据库有哪些

商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等;
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL语言定义了哪几种操作数据库的能力

1.DDL：Data Definition Language，允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
2.DML：Data Manipulation Language，DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
3.DQL：Data Query Language，DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

关系模型

  • 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
  • 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL
  • NULL不表示0或者空字符串‘’，表示的是字段数据不存在。
  • 通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。
  • 和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
  • 主键也是一个字段，通过主键可以唯一确定一条记录。所有主键的选取是十分重要的，主键不要带有业务含义，如身份证号、手机号、邮箱地址，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。
  • 关系数据库通过外键可以实现一对多、多对多和一对一的关系。

查询数据

  • 要查询一张表的数据，使用：SELECT FROM <表名>，SELECT是关键字，表示要执行一次查询，表示所有列，FROM表示要从哪个表来查询
  • SELECT语句可以通过WHERE来设定查询条件，查询结果是满足查询条件的记录，即SELECT FROM <表名> WHERE <条件表达式>，如：SELECT FROM students WHERE score >= 80，score是列名
  • 条件表达式：
    • <条件1> AND <条件2>：表示同时满足条件1条件2
    • <条件1> OR <条件2>：表示满足条件1或条件2
    • NOT <条件>：表示“不符合该条件”的记录
  • 查询部分列：SELECT 列1, 列2, 列3 FROM <表名>，并且可以对这些列进行重命名：SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM <表名>
  • 查询的结果默认是按主键排序的，而加上ORDER BY <列名>，表示根据该列按升序排列，使用ORDER BY <列名> DESC，表示按降序，对于相同的数据可以进一步排序：ORDER BY <列名1> DESC, <列名2>
  • 使用LIMIT OFFSET 可以对结果集进行分页，每次查询返回结果集的一部分，LIMIT值是每页显示的个数，OFFSET值表示跳过前多少个；在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。
  • 聚合查询：
    • COUNT()：表示查询所有列的行数，如: SELECT COUNT(*) num FROM students; 会返回students表的总行数，且命名为num
    • SUM(id) 计算某一列的合计值，该列必须为数值类型
    • AVG(id) 计算某一列的平均值，该列必须为数值类型
    • MAX(id) 计算某一列的最大值，如果是字符类型，会返回排序最后的字符
    • MIN(id) 计算某一列的最小值，如果是字符类型，会返回排序最前的字符
    • 可以综合起来使用，如：SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender; 其中GROUP BY子句指定了按class_id和gender来分组，会返回如下：
  • 多表查询
    • SELECT * FROM <表1>, <表2> 表示表1和表2的“乘积”，列数是两表列数之和，行数是两表行数之积
    • 多表查询时，可以使用表名.列名来SELECT这个列
    • 可以使用FROM <表1> 别名1, <表2> 别名2，来给表1表2起别名，使用别名不是必须的，但可以更好地简化查询语句。
    • 多表查询的结果集可能非常巨大，要小心使用
  • 连接查询
    • 先确定主表，仍然使用FROM <表1>的语法；
    • 再确定需要连接的表，使用INNER JOIN <表2>的语法；
    • 然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
    • 可选：加上WHERE子句、ORDER BY等子句。
    • RIGHT OUTER JOIN返回右表都存在的行，如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段
    • LEFT OUTER JOIN则返回左表都存在的行，如果某一行仅在左表存在，那么结果集就会以NULL填充剩下的字段
    • FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

修改数据

  • INSERT：
    • 基本语法为：INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
    • 值的顺序必须和字段顺序一致
    • 还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(…)包含的一组值：
  • UPDATE：
    • 基本语法为：UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
    • 在字段1=值1, 字段2=值2… 写出要更新的值
    • 在WHERE子句中写出需要更新的行的筛选条件
    • 更新字段时可以使用表达式，如：score=score+10，表示符合where条件的分数都加10
    • 注意：如果没有where条件，则所有的记录都将被update，因此，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。
  • DELETE
    • 基本语法为：DELETE FROM <表名> WHERE …;
    • 和UPDATE相同，如果没有where条件，则会删除整个表的数据

MySQL

  • 列出全部的数据库：SHOW DATABASES;其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们
  • 创建一个新数据库：CREATE DATABASE test;
  • 删除一个数据库：DROP DATABASE test;
  • 对一个数据库进行操作时，要首先将其切换为当前数据库：USE test;
  • 列出当前数据库的所有表：SHOW TABLES;
  • 要查看一个表的结构：DESC <表名>;
  • 删除表：DROP TABLE students;
  • 插入或替换：REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99);
  • 插入或更新：INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99) ON DUPLICATE KEY UPDATE name=’小明’, gender=’F’, score=99;
  • 插入或忽略：INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明’, ‘F’, 99);
  • 对一个表进行快照，即复制一份当前表的数据到一个新表：CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

事物

  • 数据库事物：把多条语句作为一个整体进行操作的功能，数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败
  • 具有ACID四个特性：
    • Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
    • Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
    • Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
    • Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。
  • 对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
  • 要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务
  • COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存
  • 有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

参考资料

廖雪峰SQL教程

-------------本文结束 感谢您的阅读-------------